-- **************************************************************************
--    Project Name:   全程时效-紫金山虚假发货监控明细表
--    Job Name:       jms_dm.dm_sham_send_monitor_detail_dt
--    Author :        季修魁
--    date：          2023/09/12
-- **************************************************************************
-- **************************************************************************


set spark.sql.crossJoin.enabled=true;

---运单轨迹表
with trace_waybill_step_tab as ( --单号,发件站点,轨迹下一站点,轨迹上一站点（剔除集散）
select waybill_no                                                                                                as waybill_no
       ,send_station_network_code                                                                                as send_station_network_code
       ,send_station_network_type                                                                                as send_station_network_type
       ,send_station_number                                                                                      as send_station_number
       ,send_station_send_later_time                                                                             as send_station_send_time
       ,send_station_arrival_early_time                                                                          as send_station_arrival_time
       ,send_center_station_number                                                                               as send_center_station_number
       ,lead(send_station_network_code,1,null) over (partition by waybill_no order by send_station_number)       as next_station_network_code
       ,lead(send_station_send_later_time,1,null) over (partition by waybill_no order by send_station_number)    as next_station_send_time
       ,lead(send_station_arrival_early_time,1,null) over (partition by waybill_no order by send_station_number) as next_station_arrival_time
       ,lead(send_station_network_code,2,null) over (partition by waybill_no order by send_station_number)       as next_next_station_network_code
       ,lead(send_station_send_later_time,2,null) over (partition by waybill_no order by send_station_number)    as next_next_station_send_time
       ,lead(send_station_arrival_early_time,2,null) over (partition by waybill_no order by send_station_number) as next_next_station_arrival_time
       ,lag( send_station_network_code,1,null) over (partition by waybill_no order by send_station_number)       as last_station_network_code
       ,lag( send_station_send_later_time,1,null) over (partition by waybill_no order by send_station_number)    as last_station_send_time
       ,lag( send_station_arrival_early_time,1,null) over (partition by waybill_no order by send_station_number) as last_station_arrival_time
       ,row_number() over(partition by waybill_no order by send_station_number )                                 as new_send_station_number
       ,count(waybill_no) over(partition by waybill_no  )                                                        as station_number
       ,dt                                                                                                       as dt
  from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
 where dt>=date_add('{{ execution_date | cst_ds }}',-30) and dt<='{{ execution_date | cst_ds }}'
   and ordersource_name='紫金山'
   and send_station_network_type<>'3'   ---剔除轨迹为集散的
),

trace_waybill_step_tab1 as (
select a.*
       ,b.name                                               as send_station_network_name
       ,b.agent_code                                         as send_station_agent_code
       ,b.agent_name                                         as send_station_agent_name
       ,b.city_id                                            as send_station_city_id
       ,b.city_desc                                          as send_station_city_desc
       ,b.provider_id                                        as send_station_provider_id
       ,b.provider_desc                                      as send_station_provider_desc
       ,c.name                                               as next_station_network_name
       ,c.network_type                                       as next_station_network_type
       ,c.agent_code                                         as next_station_agent_code
       ,c.agent_name                                         as next_station_agent_name
       ,c.city_id                                            as next_station_city_id
       ,c.city_desc                                          as next_station_city_desc
       ,c.provider_id                                        as next_station_provider_id
       ,c.provider_desc                                      as next_station_provider_desc
       ,d.name                                               as next_next_station_network_name
       ,d.network_type                                       as next_next_station_network_type
       ,d.agent_code                                         as next_next_station_agent_code
       ,d.agent_name                                         as next_next_station_agent_name
       ,d.city_id                                            as next_next_station_city_id
       ,d.city_desc                                          as next_next_station_city_desc
       ,d.provider_id                                        as next_next_station_provider_id
       ,d.provider_desc                                      as next_next_station_provider_desc
       ,e.name                                               as last_station_network_name
       ,e.network_type                                       as last_station_network_type
       ,e.agent_code                                         as last_station_agent_code
       ,e.agent_name                                         as last_station_agent_name
       ,e.city_id                                            as last_station_city_id
       ,e.city_desc                                          as last_station_city_desc
       ,e.provider_id                                        as last_station_provider_id
       ,e.provider_desc                                      as last_station_provider_desc
       ,least(a.next_station_arrival_time,a.next_station_send_time) as next_station_scan_time  --下一站点扫描时间=min(下一站到件时间，下一站发件时间)
       ,least(a.next_next_station_arrival_time,a.next_next_station_send_time) as next_next_station_scan_time --下下一站点扫描时间=min(下一站到件时间，下一站发件时间)
       ,min(if(send_station_network_type!=2,999,new_send_station_number)) over (partition by waybill_no )   as first_center_station_number --首中心序号
  from trace_waybill_step_tab                       a
  left join jms_dim.dim_network_whole_massage       b
    on a.send_station_network_code = b.code
  left join jms_dim.dim_network_whole_massage       c
    on a.next_station_network_code = c.code
  left join jms_dim.dim_network_whole_massage       d
    on a.next_next_station_network_code = d.code
  left join jms_dim.dim_network_whole_massage       e
    on a.last_station_network_code = e.code
),

---出仓网点

tab_delivery as (
    select  t.waybill_no                 as waybill_no
           ,t.scan_site_code             as scan_site_code
           ,t.scan_site                  as scan_site
           ,t.scan_time                  as scan_time
           ,net.name                     as deliver_network_name
           ,net.agent_code               as deliver_agent_code
           ,net.agent_name               as deliver_agent_name
           ,net.city_id                  as deliver_city_id
           ,net.city_desc                as deliver_city_desc
           ,net.provider_id              as deliver_provider_id
           ,net.provider_desc            as deliver_provider_desc
    from (select waybill_no,
                 scan_site_code,
                 scan_site,
                 scan_time,
                 row_number() over(partition by waybill_no order by scan_time) rk
           from jms_dwd.dwd_tab_barscan_deliver_base_dt
          where dt>=date_add('{{ execution_date | cst_ds }}',-30) and dt<='{{ execution_date | cst_ds }}'
         ) t
   left join jms_dim.dim_network_whole_massage    net
     on t.scan_site_code = net.code
  where rk = 1
),

---退转件
reback_transfer_tbl as (
select *
  from (
 select waybill_no
        ,apply_time
        ,apply_network_code
        ,apply_network_name
        ,row_number() over(partition by waybill_no order by apply_time) rk
 from jms_dwd.dwd_tab_reback_transfer_express_base_dt
 where dt >= date_add('{{ execution_date | cst_ds }}', -60)
   and dt <= '{{ execution_date | cst_ds }}'
   and apply_type_code in (4) --申请类型4:退回
   and status not in (3, 4) --状态 1待审核，2已审核,3 取消申请,4.驳回
 ) a where rk=1
),

----运单宽表

wide_unsign_summary_waybill_tab as ( --单号，始端站点信息（揽收，入仓，录入网点，始发中心），末端网点信息（派件网点，末端中心）
select t1.waybill_no
       ,t1.input_time
       ,t1.input_network_code
       ,t1.taking_scantime
       ,t1.taking_early_scansitecode
       ,t1.warehousing_early_scantime
       ,t1.warehousing_early_scansitecode
       ,t1.first_network_code
       ,t1.end_network_code
       ,t1.sign_time
       ,t1.is_reback
       ,coalesce(t1.first_center_network_code,route1.in_to_code)  as first_center_network_code
       ,t1.end_center_network_code
       ,t1.taking_scansitecode
       ,t1.ordersource_code
       ,t1.ordersource_name
       ,to_date(taking_scantime)            as  dt
  from (
select waybill_no                                                                                    as waybill_no
       ,input_time                                                                                   as input_time
       ,input_network_code                                                                           as input_network_code
       ,coalesce(taking_early_scantime,warehousing_early_scantime,input_time)                        as taking_scantime
       ,taking_early_scansitecode                                                                    as taking_early_scansitecode
       ,warehousing_early_scantime                                                                   as warehousing_early_scantime
       ,warehousing_early_scansitecode                                                               as warehousing_early_scansitecode
       ,coalesce(taking_early_scansitecode,warehousing_early_scansitecode,input_network_code)        as first_network_code
       ,dispatch_network_code                                                                        as end_network_code
       ,coalesce(sign_early_scantime,outstore_early_scantime)                                        as sign_time
       ,is_reback                                                                                    as is_reback
       ,first_center_network_code                                                                    as first_center_network_code
       ,end_center_network_code                                                                      as end_center_network_code
       ,taking_early_scansitecode                                                                    as taking_scansitecode
       ,ordersource_name
       ,ordersource_code
       ,dt                                                                                           as dt
  from jms_dwd.dwd_wide_unsign_summary_waybill_dt
 where dt>=date_add('{{ execution_date | cst_ds }}',-30)
   and dt<='{{ execution_date | cst_ds }}'
   and ordersource_name='紫金山'
   and nvl(end_piece_type_code,'0')<>'5'             ---剔除客户取消寄件  ---add jixk 2023-11-02
   )  t1
  left join (select in_from_code,out_to_code,in_from_name,out_to_name,max(in_to_code)  as in_to_code  --随机的始发中心
               from jms_dm.dm_route_fastest                          ----最快路由表数据
               where dt = '{{ execution_date | cst_ds }}'
               group by in_from_code,out_to_code,in_from_name,out_to_name
             )   route1
   on t1.first_network_code = route1.in_from_code
  and t1.end_network_code=route1.out_to_code
 ),



waybill_main_info_tab as (
select  a.waybill_no                                         as waybill_no
       ,a.input_time                                         as input_time
       ,a.input_network_code                                 as input_network_code
       ,a.taking_scantime                                    as taking_scantime
       ,a.taking_scansitecode                                as taking_scansitecode
       ,a.warehousing_early_scantime                         as warehousing_scantime
       ,a.warehousing_early_scansitecode                     as warehousing_scansitecode
       ,a.first_network_code                                 as first_network_code
       ,c.name                                               as first_network_name
       ,c.agent_code                                         as first_agent_code
       ,c.agent_name                                         as first_agent_name
       ,c.city_id                                            as first_city_id
       ,c.city_desc                                          as first_city_desc
       ,c.provider_id                                        as first_provider_id
       ,c.provider_desc                                      as first_provider_desc
       ,a.end_network_code                                   as end_network_code
       ,d.name                                               as end_network_name
       ,d.agent_code                                         as end_agent_code
       ,d.agent_name                                         as end_agent_name
       ,d.city_id                                            as end_city_id
       ,d.city_desc                                          as end_city_desc
       ,d.provider_id                                        as end_provider_id
       ,d.provider_desc                                      as end_provider_desc
     ---关联派件网点取的信息
       ,b.scan_site_code                                     as deliver_network_code
       ,b.deliver_network_name                               as deliver_network_name
       ,b.deliver_agent_code                                 as deliver_agent_code
       ,b.deliver_agent_name                                 as deliver_agent_name
       ,b.deliver_city_id                                    as deliver_city_id
       ,b.deliver_city_desc                                  as deliver_city_desc
       ,b.deliver_provider_id                                as deliver_provider_id
       ,b.deliver_provider_desc                              as deliver_provider_desc
       ,b.scan_time                                          as deliver_time

       ,a.sign_time                                          as sign_time
       ,a.is_reback                                          as is_reback
       ,a.first_center_network_code                          as first_center_network_code
       ,f.name                                               as first_center_network_name
       ,f.city_id                                            as first_center_city_id
       ,f.city_desc                                          as first_center_city_desc
       ,f.provider_id                                        as first_center_provider_id
       ,f.provider_desc                                      as first_center_provider_desc
       ,a.end_center_network_code                            as end_center_network_code
       ,case when g.waybill_no is not null then 1 else 0 end as is_refund
       ,g.apply_time                                         as refund_date
       ,g.apply_network_code                                 as refund_apply_network_code
       ,g.apply_network_name                                  as refund_apply_network_name
       ,a.ordersource_name
       ,a.ordersource_code
       ,a.dt                                                 as dt
  from wide_unsign_summary_waybill_tab                a
  left join tab_delivery                              b
    on a.waybill_no = b.waybill_no
  left join jms_dim.dim_network_whole_massage         c
    on a.first_network_code = c.code
  left join jms_dim.dim_network_whole_massage         d
    on a.end_network_code = d.code
  left join jms_dim.dim_network_whole_massage         f
    on a.first_center_network_code = f.code
  left join reback_transfer_tbl                       g
    on a.waybill_no = g.waybill_no
),

waybill_main_info_test1 as (
----insert overwrite table bidefault.waybill_main_info_test1 partition (dt)
--运单始末信息
select a.waybill_no
      ,a.input_time
      ,a.input_network_code
      ,a.taking_scantime
      ,a.taking_scansitecode
      ,a.warehousing_scantime
      ,a.warehousing_scansitecode
      ,a.first_network_code
      ,a.first_network_name
      ,a.first_agent_code
      ,a.first_agent_name
      ,a.first_city_id
      ,a.first_city_desc
      ,a.first_provider_id
      ,a.first_provider_desc
      ,a.end_network_code
      ,a.end_network_name
      ,a.end_agent_code
      ,a.end_agent_name
      ,a.end_city_id
      ,a.end_city_desc
      ,a.end_provider_id
      ,a.end_provider_desc
      ,a.deliver_network_code
      ,a.deliver_network_name
      ,a.deliver_agent_code
      ,a.deliver_agent_name
      ,a.deliver_city_id
      ,a.deliver_city_desc
      ,a.deliver_provider_id
      ,a.deliver_provider_desc
      ,a.sign_time
      ,a.is_reback
      ,a.is_refund
      ,a.refund_date
      ,a.first_center_network_code
      ,a.first_center_city_id
      ,a.first_center_city_desc
      ,a.first_center_provider_id
      ,a.first_center_provider_desc
      ,a.end_center_network_code
      ,a.deliver_time
     --------轨迹
      ,b.send_station_network_code
      ,b.send_station_network_name
      ,b.send_station_provider_id
      ,b.send_station_provider_desc
      ,b.send_station_city_id
      ,b.send_station_city_desc
      ,b.send_station_network_type
      ,b.send_station_number
      ,b.new_send_station_number
      ,case when b.send_station_network_code=a.first_network_code and b.send_station_number='1' then coalesce(b.send_station_arrival_time,a.taking_scantime) else b.send_station_arrival_time end send_station_arrival_time
      ,case when a.is_refund = '0' and b.send_station_network_code=a.end_network_code  and send_station_number<>'1' then coalesce(b.send_station_send_time,a.deliver_time) else b.send_station_send_time end send_station_send_time
      ,b.send_center_station_number
      ,b.next_station_network_code
      ,b.next_station_network_name
      ,b.next_station_provider_id
      ,b.next_station_provider_desc
      ,b.next_station_city_id
      ,b.next_station_city_desc
      ,b.next_station_network_type
      ,b.next_station_arrival_time
      ,b.next_station_send_time
      ,b.last_station_network_code
      ,b.last_station_network_name
      ,b.last_station_network_type
      ,b.last_station_provider_id
      ,b.last_station_provider_desc
      ,b.last_station_city_id
      ,b.last_station_city_desc
      ,b.last_station_arrival_time
      ,b.last_station_send_time
      ,b.next_next_station_network_code
      ,b.next_next_station_network_name
      ,b.next_next_station_network_type
      ,b.next_next_station_arrival_time
      ,b.next_next_station_send_time
      ,b.station_number
      ,a.ordersource_name
      ,a.ordersource_code
      ,a.refund_apply_network_code
      ,a.refund_apply_network_name
      ,b.next_station_scan_time
      ,b.next_next_station_scan_time
      ,b.first_center_station_number --首中心序号
      ,a.dt    as dt
from waybill_main_info_tab                        a        ---运单宽表
left join trace_waybill_step_tab1                 b        ---轨迹表
  on a.waybill_no = b.waybill_no
where unix_timestamp(coalesce(b.send_station_send_time,'2900-12-31 00:00:01')) <=unix_timestamp(coalesce(a.deliver_time,'3000-12-31 00:00:01'))
),

----整合明细

---insert overwrite table bidefault.waybill_main_info_test_zjs_1 partition (dt)
waybill_main_info_test2 as (  --计算允许超时时间
select  a.waybill_no
       ,a.input_time
       ,a.input_network_code
       ,a.taking_scantime
       ,a.taking_scansitecode
       ,a.warehousing_scantime
       ,a.warehousing_scansitecode
       ,a.first_network_code
       ,a.first_network_name
       ,a.first_agent_code
       ,a.first_agent_name
       ,a.first_city_id
       ,a.first_city_desc
       ,a.first_provider_id
       ,a.first_provider_desc
       ,a.end_network_code
       ,a.end_network_name
       ,a.end_agent_code
       ,a.end_agent_name
       ,a.end_city_id
       ,a.end_city_desc
       ,a.end_provider_id
       ,a.end_provider_desc
       ,a.deliver_network_code
       ,a.deliver_network_name
       ,a.deliver_agent_code
       ,a.deliver_agent_name
       ,a.deliver_city_id
       ,a.deliver_city_desc
       ,a.deliver_provider_id
       ,a.deliver_provider_desc
       ,a.sign_time
       ,a.is_reback
       ,a.is_refund
       ,a.refund_date
       ,a.first_center_network_code
       ,a.first_center_city_id
       ,a.first_center_city_desc
       ,a.first_center_provider_id
       ,a.first_center_provider_desc
       ,a.end_center_network_code
       ,a.deliver_time
       ,a.send_station_network_code
       ,a.send_station_network_name
       ,a.send_station_provider_id
       ,a.send_station_provider_desc
       ,a.send_station_city_id
       ,a.send_station_city_desc
       ,a.send_station_network_type
       ,a.send_station_number
       ,a.new_send_station_number
       ,a.send_station_arrival_time
       ,a.send_station_send_time
       ,a.send_center_station_number
       ,a.next_station_network_code
       ,a.next_station_network_name
       ,a.next_station_provider_id
       ,a.next_station_provider_desc
       ,a.next_station_city_id
       ,a.next_station_city_desc
       ,a.next_station_network_type
       ,a.next_station_arrival_time
       ,a.next_station_send_time
       ,a.last_station_network_code
       ,a.last_station_network_name
       ,a.last_station_network_type
       ,a.last_station_provider_id
       ,a.last_station_provider_desc
       ,a.last_station_city_id
       ,a.last_station_city_desc
       ,a.last_station_arrival_time
       ,a.last_station_send_time
       ,a.next_next_station_network_code
       ,a.next_next_station_network_name
       ,a.next_next_station_network_type
       ,a.next_next_station_arrival_time
       ,a.next_next_station_send_time
       ,a.ordersource_name
       ,a.ordersource_code
       ,case when send_station_send_time is not null then (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) /3600
             else (unix_timestamp()-unix_timestamp(send_station_arrival_time)) /3600
        end  as first_network_stay_duration            ---当前站点停留时间
       ,case when next_station_arrival_time is not null then (unix_timestamp(next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             else (unix_timestamp()-unix_timestamp(send_station_send_time)) /3600
        end  as first_network_runing_duration          ---当前站点发出运行时间
       ,case when a.first_provider_id=a.first_center_provider_id
                  and (a.first_provider_id not in ('650000','540000','150000','640000','630000','620000','460000','530000') or a.first_city_id='530100')
             then concat(date_add(to_date(a.send_station_arrival_time),c.timeout_days),' ',c.timeout_time,":00")           ---同省非偏远
             when a.first_provider_id=a.first_center_provider_id
                  and a.first_provider_id in ('650000','540000','150000','640000','630000','620000','460000','530000')  and a.first_city_id<>'530100'
             then concat(date_add(to_date(a.send_station_arrival_time),e.timeout_days),' ',e.timeout_time,":00")         ---同省偏远
             when a.first_provider_id<>a.first_center_provider_id
             then concat(date_add(to_date(a.send_station_arrival_time),d.timeout_days),' ',d.timeout_time,":00")
        end first_network_stay_timeout_time
       ,case when a.first_provider_id=a.first_center_provider_id
                  and (a.first_provider_id not in ('650000','540000','150000','640000','630000','620000','460000','530000') or a.first_city_id='530100' )
             then concat(date_add(to_date(a.send_station_send_time),c.timeout_days),' ',c.timeout_time,":00")
             when a.first_provider_id=a.first_center_provider_id
                  and a.first_provider_id in ('650000','540000','150000','640000','630000','620000','460000','530000') and a.first_city_id<>'530100'
             then concat(date_add(to_date(a.send_station_send_time),e.timeout_days),' ',e.timeout_time,":00")
             when a.first_provider_id<>a.first_center_provider_id
             then concat(date_add(to_date(a.send_station_send_time),d.timeout_days),' ',d.timeout_time,":00")
        end first_network_runing_timeout_time
       ,case when a.first_provider_id=a.first_center_provider_id
                  and (a.first_provider_id not in ('650000','540000','150000','640000','630000','620000','460000','530000') or a.first_city_id='530100' )
             then concat('第',c.timeout_days,'天',c.timeout_time,'点前')
             when a.first_provider_id=a.first_center_provider_id
                  and a.first_provider_id in ('650000','540000','150000','640000','630000','620000','460000','530000') and a.first_city_id<>'530100'
             then concat('第',e.timeout_days,'天',e.timeout_time,'点前')
             when a.first_provider_id<>a.first_center_provider_id
             then concat('第',d.timeout_days,'天',d.timeout_time,'点前')
        end first_network_timeout_desc
       ,from_unixtime(unix_timestamp(a.send_station_arrival_time)+(3600 * b.timeout_hours),'yyyy-MM-dd HH:mm:ss')  as transit_stay_timeout_time
       ,from_unixtime(unix_timestamp(a.send_station_send_time)+(3600 * b.timeout_hours),'yyyy-MM-dd HH:mm:ss')     as transit_runing_timeout_time
       ,concat('超过',b.timeout_hours,'小时')                                                                       as transit_timeout_desc
       ,a.station_number
       ,a.refund_apply_network_code   ---add jixk 2023-11-02
       ,a.refund_apply_network_name      ---add jixk 2023-11-02
       ,a.next_station_scan_time
       ,a.next_next_station_scan_time
       ,a.first_center_station_number --首中心序号
       ,a.dt
  from (select * from waybill_main_info_test1
         where (unix_timestamp(coalesce(send_station_send_time,'2999-01-01 00:00:01')) <unix_timestamp(coalesce(refund_date,'3339-01-01 00:00:01'))) or send_station_number=1
           and dt>=date_sub('{{ execution_date | cst_ds }}',16) and dt<='{{ execution_date | cst_ds }}'
         ) a
  left join (select substr(start_province,1,2) as start_province,substr(sign_province,1,2) as sign_province,timeout_hours
               from jms_dim.dim_tab_false_ship_delivery_cfg
               where state=1
                and platform='紫金山'
              group by substr(start_province,1,2),substr(sign_province,1,2),timeout_hours ) b
    on substr(a.first_provider_desc,1,2)=substr(b.start_province,1,2)
   and substr(a.end_provider_desc,1,2)=substr(b.sign_province,1,2)
  left join (select * from jms_dim.dim_tab_false_ship_collect_cfg where state=1 and standard=1)  c      ----同省非偏远
    on 1=1
  left join (select * from jms_dim.dim_tab_false_ship_collect_cfg where state=1 and standard=3)  d       ----不同省
    on 1=1
  left join (select * from jms_dim.dim_tab_false_ship_collect_cfg where state=1 and standard=2)  e        ----同省偏远
    on 1=1
),

-------------------判责处理

----start—duty---

----1.始发网点停留超时
waybill_duty_info_1 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
        --若当前节点轨迹未发件，其他机构无操作：当前时间-最早揽收时间；
       ,case when send_station_send_time is not null and send_station_send_time >first_network_stay_timeout_time then '始发网点停留超时'
            --若本网点无发件，其他机构有操作，停留时长直接达标，不判责
             when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp() >first_network_stay_timeout_time then '始发网点停留超时'
             else null
        end  as duty_type
       ,case when send_station_send_time is not null and send_station_send_time >first_network_stay_timeout_time then first_network_timeout_desc
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
             when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp()>first_network_stay_timeout_time then first_network_timeout_desc
             else null
        end  as duty_standard

       ,case when send_station_send_time is not null and send_station_send_time >first_network_stay_timeout_time then send_station_network_code
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
             when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp()>first_network_stay_timeout_time then send_station_network_code
             else null
        end  as duty_network_code
       --如果无始发网点发件 可能是中心
       ,case when send_station_send_time is not null and send_station_send_time >first_network_stay_timeout_time then send_station_network_name
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
             when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp()>first_network_stay_timeout_time then send_station_network_name
             else null
        end  as duty_network_name

       ,case when send_station_send_time is not null and send_station_send_time >first_network_stay_timeout_time then (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) /3600
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
             when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp()>first_network_stay_timeout_time then (unix_timestamp()-unix_timestamp(send_station_arrival_time)) /3600
             else null
        end  as duration
       ,first_center_station_number --首中心序号
       ,dt
 from waybill_main_info_test2
where send_station_network_code=first_network_code and send_station_number = '1' and send_station_network_code<>coalesce(deliver_network_code,end_network_code) --- add jixk 2023-11-02
  and first_network_code<>coalesce(refund_apply_network_code,0)   ---add jixk 2023-11-02
),

----2.始发网点-始发中心超时
waybill_duty_info_2 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
        /*
       ,case --存在A到件时间（且小于退件时间）：A最早的到件时间-a最新的发件时间 -->A最早的到件时间<a最新的发件时间 超时时长
             when next_station_arrival_time is not null and next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_arrival_time >first_network_runing_timeout_time
             then '始发网点-始发中心超时'
             when next_station_arrival_time is null and next_next_station_arrival_time is not null and next_next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01')
             and next_next_station_arrival_time>first_network_runing_timeout_time
             then '始发网点-始发中心超时'
             when next_station_arrival_time is null and next_next_station_arrival_time is null and current_timestamp()>first_network_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发网点-始发中心超时'
             else null
        end  as duty_type */
       ,case --A最早的到件时间或者发件-a最新的发件时间
            when next_station_scan_time is not null and next_station_scan_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_scan_time >first_network_runing_timeout_time
                then '始发网点-始发中心超时'
            --若无A及后续操作机构：当前时间-a最新的发件时间
            when next_station_scan_time is null and next_station_scan_time is null and current_timestamp()>first_network_runing_timeout_time
                and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
                then '始发网点-始发中心超时'
            else null
        end  as duty_type
       /*
       ,case when next_station_arrival_time is not null and next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_arrival_time >first_network_runing_timeout_time
             then first_network_timeout_desc
             when next_station_arrival_time is null and next_next_station_arrival_time is not null and next_next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01')
             and next_next_station_arrival_time>first_network_runing_timeout_time
             then first_network_timeout_desc
             when next_station_arrival_time is null and next_next_station_arrival_time is null and current_timestamp()>first_network_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then first_network_timeout_desc
             else null
        end  as duty_standard
       */
       ,case when next_station_scan_time is not null and next_station_scan_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_scan_time >first_network_runing_timeout_time
                 then first_network_timeout_desc
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>first_network_runing_timeout_time
                 and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
                 then first_network_timeout_desc
             else null
        end  as duty_standard
        /*
       ,case when next_station_arrival_time is not null and next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_arrival_time >first_network_runing_timeout_time
             then send_station_network_code
             when next_station_arrival_time is null and next_next_station_arrival_time is not null and next_next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01')
             and next_next_station_arrival_time>first_network_runing_timeout_time
             then send_station_network_code
             when next_station_arrival_time is null and next_next_station_arrival_time is null and current_timestamp()>first_network_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             else null
        end  as duty_network_code
       */
       ,case when next_station_scan_time is not null and next_station_scan_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_scan_time >first_network_runing_timeout_time
                 then send_station_network_code
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>first_network_runing_timeout_time
                 and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
                 then send_station_network_code
             else null
        end  as duty_network_code
       /*
       ,case when next_station_arrival_time is not null and next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_arrival_time >first_network_runing_timeout_time
             then send_station_network_name
             when next_station_arrival_time is null and next_next_station_arrival_time is not null and next_next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01')
             and next_next_station_arrival_time>first_network_runing_timeout_time
             then send_station_network_name
             when next_station_arrival_time is null and next_next_station_arrival_time is null and current_timestamp()>first_network_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             else null
        end  as duty_network_name
       */
       ,case when next_station_scan_time is not null and next_station_scan_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_scan_time >first_network_runing_timeout_time
                 then send_station_network_name
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>first_network_runing_timeout_time
                 and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
                 then send_station_network_name
             else null
       end  as duty_network_name
       /*
       ,case when next_station_arrival_time is not null and next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_arrival_time >first_network_runing_timeout_time
             then (unix_timestamp(next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_arrival_time is null and next_next_station_arrival_time is not null and next_next_station_arrival_time<nvl(refund_date,'3000-12-31 00:00:01')
             and next_next_station_arrival_time>first_network_runing_timeout_time
             then (unix_timestamp(next_next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_arrival_time is null and next_next_station_arrival_time is null and current_timestamp()>first_network_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_send_time)) /3600
             else null
        end  as duration
        */
       ,case when next_station_scan_time is not null and next_station_scan_time<nvl(refund_date,'3000-12-31 00:00:01') and next_station_scan_time >first_network_runing_timeout_time
                 then (unix_timestamp(next_station_scan_time)-unix_timestamp(send_station_send_time)) /3600
              when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>first_network_runing_timeout_time
                 and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
                 then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_send_time)) /3600
             else null
    end  as duration
       ,first_center_station_number
       ,dt
 from waybill_main_info_test2
where send_station_network_code=first_network_code and send_station_number = '1'
  and unix_timestamp(coalesce(next_station_scan_time,'2999-12-31 00:00:01'))<=unix_timestamp(coalesce(refund_date,'3000-12-31 00:00:01'))
  and next_station_network_code=first_center_network_code
----  and send_station_network_code<>refund_apply_network_code   ---add jixk 2023-11-02
),

----3.始发中心停留超时  （中转机构停留超时）
waybill_duty_info_3 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case --存在A发件时间：A最晚的发件时间-A最早的到件时间
             when send_station_network_code=first_center_network_code and send_center_station_number='1'
             and send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then '始发中心停留超时'
             when send_station_network_code=first_center_network_code and send_center_station_number='1'
             and send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp() >transit_stay_timeout_time then '始发中心停留超时'

             when send_station_network_code<>first_center_network_code and send_station_network_code<>end_center_network_code
             and send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then '中转机构停留超时'
             when send_station_network_code<>first_center_network_code and send_station_network_code<>end_center_network_code
             and send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp() >transit_stay_timeout_time then '中转机构停留超时'

             when send_station_network_code=end_center_network_code and send_center_station_number<>'1'
             and send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then '末端中心超时'
             when send_station_network_code=end_center_network_code and send_center_station_number<>'1'
             and send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
             and current_timestamp() >transit_stay_timeout_time then '末端中心超时'
             else null
        end  as duty_type
     ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then transit_timeout_desc
           when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
           and current_timestamp() >transit_stay_timeout_time then transit_timeout_desc
           else null
        end  as duty_standard
     ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then send_station_network_code
           when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
            and current_timestamp() >transit_stay_timeout_time then send_station_network_code
             else null
        end  as duty_network_code
     ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then send_station_network_name
           when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
           and current_timestamp() >transit_stay_timeout_time then send_station_network_name
           else null
        end  as duty_network_name
     ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time
           then (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) /3600
           when send_station_send_time is null and coalesce(next_station_send_time,next_station_arrival_time,next_next_station_send_time,next_next_station_arrival_time,'1')='1'
           and next_station_arrival_time is null and current_timestamp() >transit_stay_timeout_time
           then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) /3600
           else null
       end  as duration
       ,first_center_station_number
       ,dt
 from waybill_main_info_test2
where send_station_network_type='2'   ---中心
),

----9.末端网点停留超时
waybill_duty_info_4 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then '末端网点停留超时'
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null and current_timestamp() >transit_stay_timeout_time then '末端网点停留超时'
             else null
        end  as duty_type
       ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then transit_timeout_desc
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
---             and current_timestamp()>transit_stay_timeout_time then transit_timeout_desc
             else null
        end  as duty_standard

       ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then send_station_network_code
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
---             and current_timestamp()>transit_stay_timeout_time then send_station_network_code
             else null
        end  as duty_network_code

       ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then send_station_network_name
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
---             and current_timestamp()>transit_stay_timeout_time then send_station_network_name
             else null
        end  as duty_network_name

       ,case when send_station_send_time is not null and send_station_send_time >transit_stay_timeout_time then (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) /3600
---             when send_station_send_time is null and next_station_send_time is null and next_next_station_arrival_time is null
---             and current_timestamp()>transit_stay_timeout_time then (unix_timestamp()-unix_timestamp(send_station_arrival_time)) /3600
             else null
        end  as duration
       ,first_center_station_number
       ,dt
 from waybill_main_info_test2
where send_station_network_code=end_network_code and send_station_number <> '1'
),

----.始发中心-中转机构
waybill_duty_info_5 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       /*
             --b网点有到件且发件，则按照A-b|b-B二个环节计算
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             --b网点有到件无发件，B存在到件，则A-b且A-B其一规则不满足都A机构为虚假发货责任机构
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01') and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             else null
        end  as duty_type
        */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             else null
             end  as duty_type
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             else null
        end  as duty_standard
        */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then transit_timeout_desc
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then transit_timeout_desc
              else null
              end  as duty_standard
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             else null
        end  as duty_network_code
        */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_code
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_code
              else null
              end  as duty_network_code
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             else null
        end  as duty_network_name
        */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_name
              when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
               and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_name
              else null
              end  as duty_network_name
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             else null
        end  as duration
       */
       ,case when next_station_scan_time is not null  and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_station_scan_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_send_time)) /3600
             else null
            end  as duration
       ,first_center_station_number
       ,dt
 from waybill_main_info_test2
where send_station_network_code=first_center_network_code
  and send_center_station_number=1
  and next_station_network_type='6' and next_next_station_network_type='4'
),

----6.中转环节流转超时

waybill_duty_info_6 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             AND greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '中转环节流转超时'
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '中转环节流转超时'
             else null
        end  as duty_type
        */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             else null
             end  as duty_type
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             else null
        end  as duty_standard
       */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             else null
             end  as duty_standard
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             else null
        end  as duty_network_code
       */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_code
              when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_code
              else null
              end  as duty_network_code
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             else null
        end  as duty_network_name
       */
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
                           then send_station_network_name
                       when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
                           and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
                           then send_station_network_name
                       else null
              end  as duty_network_name
       /*
       ,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and  greatest(next_station_arrival_time,next_station_send_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             else null
        end  as duration
       */
       ,case when next_station_scan_time is not null  and next_station_scan_time>transit_runing_timeout_time
            and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
            and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
            then (unix_timestamp(next_station_scan_time)-unix_timestamp(send_station_send_time)) /3600
            when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
            and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
            then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_send_time)) /3600
            else null
            end  as duration
       ,first_center_station_number
       ,dt
 from waybill_main_info_test2
where send_station_network_code<>first_center_network_code
  and send_station_network_code<>end_center_network_code
  and next_station_network_type='6'           ---网点
  and next_next_station_network_type='4'      ---中心
  and send_station_network_type='2'           ---中心    add jixk 2023-11-01
),

----8.末端环节流转超时
waybill_duty_info_7 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code

       /*,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '末端环节流转超时'
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '末端环节流转超时'
             else null
        end  as duty_type*/
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then '始发中心-中转机构'
             else null
             end  as duty_type

       /*,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then transit_timeout_desc
             else null
        end  as duty_standard*/
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then transit_timeout_desc
              when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then transit_timeout_desc
              else null
              end  as duty_standard
       /*,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_code
             else null
        end  as duty_network_code*/
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_code
              when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_code
              else null
              end  as duty_network_code
       /*,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then send_station_network_name
             else null
        end  as duty_network_name*/
       ,case when next_station_scan_time is not null and next_station_scan_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_name
              when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
              and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
              then send_station_network_name
              else null
              end  as duty_network_name
       /*,case when next_station_arrival_time is not null and next_station_send_time is not null and next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_arrival_time is not null and next_station_send_time is null and next_next_station_arrival_time>transit_runing_timeout_time
              and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(deliver_time,'3000-12-31 00:00:01')  and greatest(next_station_arrival_time,next_next_station_arrival_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_next_station_arrival_time)-unix_timestamp(send_station_send_time)) /3600
             else null
        end  as duration*/
       ,case when next_station_scan_time is not null  and next_station_scan_time>transit_runing_timeout_time
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(deliver_time,'3000-12-31 00:00:01')
             and greatest(next_station_arrival_time,next_station_send_time)<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(next_station_scan_time)-unix_timestamp(send_station_send_time)) /3600
             when next_station_scan_time is null and next_next_station_scan_time is null and current_timestamp()>transit_runing_timeout_time
             and current_timestamp()<nvl(refund_date,'3000-12-31 00:00:01')
             then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_send_time)) /3600
             else null
             end  as duration
       ,first_center_station_number
       ,dt
 from waybill_main_info_test2
where send_station_network_code=end_center_network_code
  and next_station_network_type='6'
  and next_next_station_network_type='4'
),

----end—duty----
--首责任机构为始发网点的
--中心到末端网点中的首责任机构
waybill_duty_info_tab1 as (
    select a.*
         ,d.network_type
         ,d.agent_code
         ,d.agent_name
         ,a.detail_type
    from (
--首责任机构
             select waybill_no
                  , split((regexp_replace(duty_type, '\\d+\:', '')), ',')[0]         as first_duty_type
                  , split((regexp_replace(duty_standard, '\\d+\a', '')), ',')[0]     as first_duty_standard
                  , split((regexp_replace(duty_network_code, '\\d+\:', '')), ',')[0] as first_duty_network_code
                  , split((regexp_replace(duty_network_name, '\\d+\:', '')), ',')[0] as first_duty_network_name
                  , duty_network_code
                  , duty_network_name
                  , regexp_replace(duration, '\\d+\:', '')                           as duration
                  , dt
                  ,1 as detail_type
             from (
                      select waybill_no                                                             as waybill_no
                           , concat_ws(',', sort_array(collect_set(tbb.duty_type)))                 as duty_type
                           , concat_ws(',', sort_array(collect_set(tbb.duty_standard)))             as duty_standard
                           , concat_ws(',', sort_array(collect_set(tbb.duty_network_code)))         as duty_network_code
                           , concat_ws(',', sort_array(collect_set(tbb.duty_network_name)))         as duty_network_name
                           , concat_ws(',', sort_array(collect_set(tbb.duration)))                  as duration
                           , dt
                      from (
                               select waybill_no
                                    , dt
                                    , concat(send_station_number, ':', duty_type)                 as duty_type
                                    , concat(send_station_number, 'a', duty_standard)             as duty_standard
                                    , concat(send_station_number, ':', duty_network_code)         as duty_network_code
                                    , concat(send_station_number, ':', duty_network_name)         as duty_network_name
                                    , concat(send_station_number, ':', ROUND(duration, 2))        as duration
                               from --bidefault.waybill_duty_info_tttt
                                   (
                                       select * from waybill_duty_info_1 where duty_network_code is not null and duty_type is not null
                                       union all
                                       select * from waybill_duty_info_2 where duty_network_code is not null and duty_type is not null
                                       union all
                                       select * from waybill_duty_info_3 where duty_network_code is not null and duty_type is not null
                                       union all
                                       select * from waybill_duty_info_4 where duty_network_code is not null and duty_type is not null
                                       union all
                                       select * from waybill_duty_info_5 where duty_network_code is not null and duty_type is not null
                                       union all
                                       select * from waybill_duty_info_6 where duty_network_code is not null and duty_type is not null
                                       union all
                                       select * from waybill_duty_info_7 where duty_network_code is not null and duty_type is not null

                                   )aaa
                           ) tbb --where waybill_no in ('JT0010698376113','JT2064492297513')
                      group by tbb.waybill_no, dt
                  ) tt
             union all
             select waybill_no
                  , split((regexp_replace(center_duty_type, '\\d+\:', '')), ',')[0]         as first_duty_type
                  , split((regexp_replace(center_duty_standard, '\\d+\a', '')), ',')[0]     as first_duty_standard
                  , split((regexp_replace(duty_center_code, '\\d+\:', '')), ',')[0]         as first_duty_network_code
                  , split((regexp_replace(duty_center_name, '\\d+\:', '')), ',')[0]         as first_duty_network_name
                  , duty_network_code
                  , duty_network_name
                  , regexp_replace(duration, '\\d+\:', '')                           as duration
                  , dt
                  , 2 as detail_type
             from (
                      select waybill_no                                                             as waybill_no
                           , concat_ws(',', sort_array(collect_set(tbb.duty_network_code)))         as duty_network_code
                           , concat_ws(',', sort_array(collect_set(tbb.duty_network_name)))         as duty_network_name
                           , concat_ws(',', sort_array(collect_set(tbb.duration)))                  as duration
                           , concat_ws(',',sort_array(collect_set(if(first_center_station_number>send_station_number,null,tbb.duty_type))))        as center_duty_type
                           , concat_ws(',',sort_array(collect_set(if(first_center_station_number>send_station_number,null,tbb.duty_standard))))    as center_duty_standard
                           , concat_ws(',',sort_array(collect_set(if(first_center_station_number>send_station_number,null,tbb.duty_network_code))))as duty_center_code
                           , concat_ws(',',sort_array(collect_set(if(first_center_station_number>send_station_number,null,tbb.duty_network_name))))as duty_center_name
                           , dt
                      from (
                               select waybill_no
                                    , dt
                                    , concat(send_station_number, ':', send_station_network_code) as send_station_network_code
                                    , concat(send_station_number, ':', send_station_network_name) as send_station_network_name
                                    , concat(send_station_number, ':', send_station_network_type) as send_station_network_type
                                    , concat(send_station_number, ':', send_station_arrival_time) as send_station_arrival_time
                                    , concat(send_station_number, ':', send_station_send_time)    as send_station_send_time
                                    , concat(send_station_number, ':', next_station_network_code) as next_station_network_code
                                    , concat(send_station_number, ':', duty_type)                 as duty_type
                                    , concat(send_station_number, 'a', duty_standard)             as duty_standard
                                    , concat(send_station_number, ':', duty_network_code)         as duty_network_code
                                    , concat(send_station_number, ':', duty_network_name)         as duty_network_name
                                    , concat(send_station_number, ':', ROUND(duration, 2))        as duration
                                    , send_station_number
                                    , nvl(net.network_type,-1)                                    as  duty_network_type
                                    --, min(send_station_number) over(partition by waybill_no,dt)   as  min_send_station_number
                                    , first_center_station_number
                               from  ---bidefault.waybill_duty_info_tttt
                                (
                                    select * from waybill_duty_info_1 where duty_network_code is not null and duty_type is not null
                                    union all
                                    select * from waybill_duty_info_2 where duty_network_code is not null and duty_type is not null
                                    union all
                                    select * from waybill_duty_info_3 where duty_network_code is not null and duty_type is not null
                                    union all
                                    select * from waybill_duty_info_4 where duty_network_code is not null and duty_type is not null
                                    union all
                                    select * from waybill_duty_info_5 where duty_network_code is not null and duty_type is not null
                                    union all
                                    select * from waybill_duty_info_6 where duty_network_code is not null and duty_type is not null
                                    union all
                                    select * from waybill_duty_info_7 where duty_network_code is not null and duty_type is not null

                                ) aaa
                                         left join jms_dim.dim_network_whole_massage net on net.code=aaa.duty_network_code
                               --where waybill_no in ('JT0010698376113','JT2064492297513')
                           ) tbb
                      group by tbb.waybill_no, dt
                  ) tt
         ) a
             left join jms_dim.dim_network_whole_massage d
                       on a.first_duty_network_code = d.code
    where length(first_duty_network_code) > 0
),

waybill_link_info_tab1 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,dt
  from (
select waybill_no                                                             as waybill_no
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_code))) as send_station_network_code
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_name))) as send_station_network_name
       ,dt
from (
    select waybill_no,dt
           ,concat(new_send_station_number,':',send_station_network_code)  as send_station_network_code
           ,concat(new_send_station_number,':',send_station_network_name)  as send_station_network_name
      from waybill_main_info_test2
      ) tbb
group by tbb.waybill_no,dt
) tt
)


insert overwrite table jms_dm.dm_sham_send_monitor_detail_dt_mid_update partition(dt,ordersource_code)
--insert overwrite table jms_dm.dm_sham_send_monitor_detail_dt_mid partition (dt,ordersource_code)
select  a.waybill_no                       as bill_code
       ,a.input_time
       ,a.taking_scantime                  as taking_time
       ,b.first_duty_network_code
       ,b.first_duty_network_name
       ,b.network_type
       ,b.agent_code
       ,b.agent_name
       ,b.first_duty_type
       ,b.first_duty_standard
       ,a.first_network_code               as   start_network_code
       ,a.first_network_name               as   start_network_name
       ,a.first_agent_code                 as   start_agent_code
       ,a.first_agent_name                 as   start_agent_name
       ,a.first_city_id                    as   start_city_id
       ,a.first_city_desc                  as   start_city_desc
       ,a.first_provider_id                as   start_provider_id
       ,a.first_provider_desc              as   start_provider_desc
       ,a.end_network_code                 as   destination_network_code
       ,a.end_network_name                 as   destination_network_name
       ,a.end_agent_code                   as   destination_agent_code
       ,a.end_agent_name                   as   destination_agent_name
       ,a.end_city_id                      as   destination_city_id
       ,a.end_city_desc                    as   destination_city_desc
       ,a.end_provider_id                 as   destination_provider_id
       ,a.end_provider_desc               as   destination_provider_desc
       ,c.send_station_network_code       as   line_link_code
       ,c.send_station_network_name       as   line_link_name
       ,b.duty_network_code               as   duty_org_code
       ,b.duty_network_name               as   duty_org_name
       ,b.duration
       ,a.deliver_network_code            as   deliver_network_code
       ,a.deliver_network_name            as   deliver_network_name
       ,a.deliver_city_id                 as   deliver_city_id
       ,a.deliver_city_desc               as   deliver_city_desc
       ,a.deliver_provider_id             as   deliver_provider_id
       ,a.deliver_provider_desc           as   deliver_provider_desc
       ,a.sign_time
       ,a.is_reback
       ,''    as fst_network_code
       ,''    as fst_network_name
       ,''    as fst_network_opt_time
       ,ordersource_name
       ,to_date(a.taking_scantime)  as taking_date
       ,to_date(a.taking_scantime)  as dt
       ,ordersource_code            as ordersource_code
  from (select * from waybill_main_info_test2 where send_station_number=1
           and to_date(taking_scantime)>=date_sub('{{ execution_date | cst_ds }}',15) and to_date(taking_scantime)<='{{ execution_date | cst_ds }}'
        )  a
  inner join (select * from waybill_duty_info_tab1  where first_duty_type  is not null )  b
    on a.waybill_no = b.waybill_no
   and a.dt=b.dt
   and ((a.first_network_code=b.first_duty_network_code and b.detail_type=1)
      or b.detail_type=2)
  left join waybill_link_info_tab1      c
    on a.waybill_no = c.waybill_no
   and a.dt=c.dt
distribute by dt,taking_date
;


----保留历史数据只写入新增的:缺点单号轨迹新增其他字段更新不了
insert overwrite table jms_dm.dm_sham_send_monitor_detail_dt_mid partition(dt,ordersource_code)
select
    bill_code
     ,input_time
     ,taking_time
     ,network_code
     ,network_name
     ,network_type
     ,agent_code
     ,agent_name
     ,fst_duty_type
     ,fst_duty_standard
     ,start_network_code
     ,start_network_name
     ,start_agent_code
     ,start_agent_name
     ,start_city_id
     ,start_city_desc
     ,start_provider_id
     ,start_provider_desc
     ,destination_network_code
     ,destination_network_name
     ,destination_agent_code
     ,destination_agent_name
     ,destination_city_id
     ,destination_city_desc
     ,destination_provider_id
     ,destination_provider_desc
     ,line_link_code
     ,line_link_name
     ,duty_org_code
     ,duty_org_name
     ,duration_hour
     ,deliver_network_code
     ,deliver_network_name
     ,deliver_city_id
     ,deliver_city_desc
     ,deliver_provider_id
     ,deliver_provider_desc
     ,sign_time
     ,is_reback
     ,fst_network_code
     ,fst_network_name
     ,fst_network_opt_time
     ,ordersource_name
     ,dt
     ,ordersource_code
from
    (
        select
            bill_code
             ,input_time
             ,taking_time
             ,network_code
             ,network_name
             ,network_type
             ,agent_code
             ,agent_name
             ,fst_duty_type
             ,fst_duty_standard
             ,start_network_code
             ,start_network_name
             ,start_agent_code
             ,start_agent_name
             ,start_city_id
             ,start_city_desc
             ,start_provider_id
             ,start_provider_desc
             ,destination_network_code
             ,destination_network_name
             ,destination_agent_code
             ,destination_agent_name
             ,destination_city_id
             ,destination_city_desc
             ,destination_provider_id
             ,destination_provider_desc
             ,line_link_code
             ,line_link_name
             ,duty_org_code
             ,duty_org_name
             ,duration_hour
             ,deliver_network_code
             ,deliver_network_name
             ,deliver_city_id
             ,deliver_city_desc
             ,deliver_provider_id
             ,deliver_provider_desc
             ,sign_time
             ,is_reback
             ,fst_network_code
             ,fst_network_name
             ,fst_network_opt_time
             ,ordersource_name
             ,dt
             ,ordersource_code
             ,row_number() over(partition by bill_code,network_code,dt order by detail_flag asc) as rn
        from (
                 select
                     bill_code
                      ,input_time
                      ,taking_time
                      ,network_code
                      ,network_name
                      ,network_type
                      ,agent_code
                      ,agent_name
                      ,fst_duty_type
                      ,fst_duty_standard
                      ,start_network_code
                      ,start_network_name
                      ,start_agent_code
                      ,start_agent_name
                      ,start_city_id
                      ,start_city_desc
                      ,start_provider_id
                      ,start_provider_desc
                      ,destination_network_code
                      ,destination_network_name
                      ,destination_agent_code
                      ,destination_agent_name
                      ,destination_city_id
                      ,destination_city_desc
                      ,destination_provider_id
                      ,destination_provider_desc
                      ,line_link_code
                      ,line_link_name
                      ,duty_org_code
                      ,duty_org_name
                      ,duration_hour
                      ,deliver_network_code
                      ,deliver_network_name
                      ,deliver_city_id
                      ,deliver_city_desc
                      ,deliver_provider_id
                      ,deliver_provider_desc
                      ,sign_time
                      ,is_reback
                      ,fst_network_code
                      ,fst_network_name
                      ,fst_network_opt_time
                      ,ordersource_name
                      ,dt
                      ,ordersource_code
                      ,1 as detail_flag
                 from jms_dm.dm_sham_send_monitor_detail_dt_mid
                 where dt between date_sub('{{ execution_date | cst_ds }}', 15) and '{{ execution_date | cst_ds }}'
                 and ordersource_code='D67'
                 union all
                 select
                     bill_code
                      ,input_time
                      ,taking_time
                      ,network_code
                      ,network_name
                      ,network_type
                      ,agent_code
                      ,agent_name
                      ,fst_duty_type
                      ,fst_duty_standard
                      ,start_network_code
                      ,start_network_name
                      ,start_agent_code
                      ,start_agent_name
                      ,start_city_id
                      ,start_city_desc
                      ,start_provider_id
                      ,start_provider_desc
                      ,destination_network_code
                      ,destination_network_name
                      ,destination_agent_code
                      ,destination_agent_name
                      ,destination_city_id
                      ,destination_city_desc
                      ,destination_provider_id
                      ,destination_provider_desc
                      ,line_link_code
                      ,line_link_name
                      ,duty_org_code
                      ,duty_org_name
                      ,duration_hour
                      ,deliver_network_code
                      ,deliver_network_name
                      ,deliver_city_id
                      ,deliver_city_desc
                      ,deliver_provider_id
                      ,deliver_provider_desc
                      ,sign_time
                      ,is_reback
                      ,fst_network_code
                      ,fst_network_name
                      ,fst_network_opt_time
                      ,ordersource_name
                      ,dt
                      ,ordersource_code
                      ,2 as detail_flag
                 from jms_dm.dm_sham_send_monitor_detail_dt_mid_update
                 where dt between date_sub('{{ execution_date | cst_ds }}', 15) and '{{ execution_date | cst_ds }}'
                 and ordersource_code='D67'
             )t
    )tt where rn=1
distribute by dt,ordersource_code;
